package com.hsnn.medstgmini.pro.project.dao.impl;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.std.model.StdCompgroup;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.BidProjCompanyDao;
import com.hsnn.medstgmini.pro.project.model.BidProjCompany;
import com.hsnn.medstgmini.pro.project.model.BidProject;
import com.hsnn.medstgmini.util.Pagination;

@Repository
public class BidProjCompanyDaoImpl extends BaseHibernateDAO implements
		BidProjCompanyDao {
	
	private static final Logger log = Logger
			.getLogger(BidProjCompanyDaoImpl.class);
	
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	@Autowired
	private HibernateSqlUtil dao;

	/**
	 * @category 已添加配送企业列表
	 * @param page
	 * @param bidProjCompany
	 * @return 已添加配送企业列表
	 */
	@SuppressWarnings("unchecked")
	public List<Object> getBidProjCompanyList(Pagination page,
			BidProjCompany bidProjCompany, StdCompinfo stdCompinfo,
			StdCompgroup stdCompgroup, BidProject bidProject) {
		
		Session session = sessionFactory.getCurrentSession();
		// 查询总记录数
		StringBuffer sql = new StringBuffer();
		sql.append(" select b.comp_code,b.comp_name,b.comp_short_name,c.comp_group_name,a.status from bid_projcompany a ");
		sql.append(" inner join std_compinfo b on b.comp_id = a.comp_id ");
		sql.append(" left join std_compgroup c on c.comp_group_id = b.comp_group_id ");
		sql.append(" where a.proj_id = :projId ");
		sql.append(" and a.`status`=1 ");
		sql.append(" and b.`status`=1 ");

		if (stdCompinfo != null) {
			if (StringUtils.isNotEmpty(stdCompinfo.getCompCode())) {
				sql.append(" and b.compCode like :compCode ");
			}
			if (StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				sql.append(" and b.compName like :compName");
			}
			if (StringUtils.isNotEmpty(stdCompinfo.getCompShortName())) {
				sql.append(" and b.compShortName like :compShortName");
			}
		}
		
		StringBuffer countSql = new StringBuffer();
		countSql.append(" select count(*) from ( ");
		countSql.append(sql.toString());
		countSql.append(" ) s where 1 = 1 ");
		Query query = session.createSQLQuery(sql.toString());
		Query countQuery = session.createSQLQuery(countSql.toString());
		query.setString("projId", bidProjCompany.getProjId());
		countQuery.setString("projId", bidProjCompany.getProjId());

		
		if (stdCompinfo != null) {
			if (StringUtils.isNotEmpty(stdCompinfo.getCompCode())) {
				query.setString("compCode", "%" + stdCompinfo.getCompCode().trim() + "%");
				countQuery.setString("compCode", "%"+ stdCompinfo.getCompCode().trim() + "%");
			}
			if (StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				query.setString("compName", "%" + stdCompinfo.getCompName().trim() + "%");
				countQuery.setString("compName", "%" + stdCompinfo.getCompName().trim() + "%");
			}
			if (StringUtils.isNotEmpty(stdCompinfo.getCompShortName())) {
				query.setString("compShortName", "%" + stdCompinfo.getCompShortName().trim() + "%");
				countQuery.setString("compShortName", "%" + stdCompinfo.getCompShortName().trim() + "%");
			}
		}
		
		BigInteger total = (BigInteger) countQuery.uniqueResult();
		page.setRecords(total.longValue());
		
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();
		
		
		/*query.setString("projId", bidProject.getProjId());

		Long total = (Long) query.uniqueResult();
		page.setRecords(total);
		// 查询分页数据
		sb.setLength(0);

		sb.append("select c, b, s from BidProjCompany b, BidProject p, StdCompinfo c, StdCompgroup s ");
		sb.append(" where b.compId = c.compId and c.status = 1 and b.projId = p.projId and c.compGroupId = s.compGroupId ");
		// 添加搜索条件
		if (stdCompinfo != null) {
			if (stdCompinfo.getCompCode() != null
					&& StringUtils.isNotEmpty(stdCompinfo.getCompCode())) {
				sb.append(" and c.compCode like :compCode ");
			}
			if (stdCompinfo.getCompName() != null
					&& StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				sb.append(" and c.compName like :compName");
			}
			if (stdCompinfo.getCompShortName() != null
					&& StringUtils.isNotEmpty(stdCompinfo.getCompShortName())) {
				sb.append(" and c.compShortName like :compShortName");
			}
		}
		sb.append(" and p.projId = :projId ");
		sb.append(" order by c.compCode asc ");
		query = session.createQuery(sb.toString());
		// 搜索条件赋值
		if (stdCompinfo != null) {
			if (stdCompinfo.getCompCode() != null
					&& StringUtils.isNotEmpty(stdCompinfo.getCompCode())) {
				query.setString("compCode", "%"
						+ stdCompinfo.getCompCode().trim() + "%");
			}
			if (stdCompinfo.getCompName() != null
					&& StringUtils.isNotEmpty(stdCompinfo.getCompName())) {
				query.setString("compName", "%"
						+ stdCompinfo.getCompName().trim() + "%");
			}
			if (stdCompinfo.getCompShortName() != null
					&& StringUtils.isNotEmpty(stdCompinfo.getCompShortName())) {
				query.setString("compShortName", "%"
						+ stdCompinfo.getCompShortName().trim() + "%");
			}
		}
		query.setString("projId", bidProject.getProjId());

		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		return query.list();*/

	}

	/**
	 * @category 启用|停用
	 * @param dicPhar
	 * @return
	 */
	public void changeStatus(BidProjCompany bidProjCompany) {
		if (bidProjCompany != null && bidProjCompany.getProjDelId() != null
				&& StringUtils.isNotEmpty(bidProjCompany.getProjDelId())) {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			sb.append("update BidProjCompany set ");
			sb.append(" status = :status ");
			sb.append(", updUser = :updUser ");
			sb.append(", updDatetime = :updDatetime ");
			sb.append(" where projDelId = :projDelId ");

			Query query = session.createQuery(sb.toString());
			query.setInteger("status", bidProjCompany.getStatus());
			query.setString("updUser", bidProjCompany.getUpdUser());
			query.setTimestamp("updDatetime", new Date());
			query.setString("projDelId", bidProjCompany.getProjDelId());
			query.executeUpdate();
			// writeLog(bidProjCompany);
			session.flush();
		}
	}

	/**
	 * @category 批量(启用|停用)
	 * @param array
	 * @return
	 */
	public void batchChangeStatus(List<BidProjCompany> list) {
		if (list != null && list.size() > 0) {
			for (BidProjCompany bidProjCompany : list) {
				changeStatus(bidProjCompany);
			}
		}
	}

	/**
	 * @category 获取配送企业数据
	 * @author 韩守松
	 * @date 2015年6月16日
	 * @param @param page
	 * @param @param bidProjCompany
	 * @param @return
	 */
	@SuppressWarnings("unchecked")
	public List<BidProjCompany> getBidProjCompanyList(Pagination page,
			BidProjCompany bidProjCompany) {
		try {
			Date date = new Date();
			SimpleDateFormat sdFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String strDate = sdFormat.format(date);
			String compName = bidProjCompany.getStdCompinfo().getCompName();
			Timestamp timeDate = Timestamp.valueOf(strDate);
			StringBuffer hql = new StringBuffer();
			StringBuffer hqlCount = new StringBuffer();
			String projId = bidProjCompany.getProjId();
			hql.append("from BidProjCompany bp where 1=1 ");
			hqlCount.append("select count(*) from BidProjCompany bp where 1=1 ");
			if (StringUtils.isNotEmpty(projId)) {
				hql.append(" and bp.projId = :projId ");
				hqlCount.append(" and bp.projId = :projId ");
			}
			if(StringUtils.isNotEmpty(compName)){
				hql.append(" and (bp.stdCompinfo.compName like :compName or bp.stdCompinfo.compName.compSpelCode like :compName)");
				hqlCount.append(" and (bp.stdCompinfo.compName like :compName or bp.stdCompinfo.compName.compSpelCode like :compName)");
			}
			hql.append(" and bp.status = 1 ");
			hql.append(" and bp.bidProject.status = 1 ");
			hql.append(" and bp.stdCompinfo.status = 1 ");
			hql.append(" and bp.stdCompinfo.compType = 2 ");
			hql.append("  and ( :timeDate between bp.bidProject.starttime and bp.bidProject.endtime ) ");
			hqlCount.append(" and bp.status = 1 ");
			hqlCount.append(" and bp.bidProject.status = 1 ");
			hqlCount.append(" and bp.stdCompinfo.status = 1 ");
			hqlCount.append(" and bp.stdCompinfo.compType = 2 ");
			hqlCount.append("  and ( :timeDate between bp.bidProject.starttime and bp.bidProject.endtime ) ");
			Query query = this.getSession().createQuery(hql.toString());
			Query queryCount = this.getSession().createQuery(hqlCount.toString());
			if (StringUtils.isNotEmpty(projId)) {
				query.setParameter("projId", projId);
				queryCount.setParameter("projId", projId);
			}
			if(StringUtils.isNotEmpty(compName)){
				query.setParameter("compName", "%"+compName+"%");
				queryCount.setParameter("compName", "%"+compName+"%");
			}
			query.setParameter("timeDate", timeDate);
			queryCount.setParameter("timeDate", timeDate);
			bidProjCompany.setCount(Long.valueOf(queryCount.list().get(0)+""));
			query.setFirstResult((page.getPage()-1)*page.getMaxResults());
			query.setMaxResults(page.getMaxResults());
			return query.list();
		} catch (Exception e) {
			log.info("查询配送企业信息异常-->", e);
			return new ArrayList<BidProjCompany>();
		}
	}

	/**
	 * @category 获取配送企业数据
	 * @author 应晓川
	 * @date   2015年7月7日
	 * @param   page
	 * @param  List<Object>
	 */
	@Override
	public List<Object> getBidProjCompanyList(Pagination page) {
	
//		String projId =  page.getConditions().get("projId");
		String compName =  page.getConditions().get("compName");
//		String hospId =  page.getConditions().get("hospId");
		String compCode =  page.getConditions().get("compCode");
		StringBuffer hql = new StringBuffer()
		.append("  select SQL_CALC_FOUND_ROWS DISTINCT sc.comp_id ,sc.comp_code ,sc.comp_name ,sc.org_code , ")
		.append("  (case when scg.comp_group_name is null then '--' else scg.comp_group_name end)            ")
		.append("  as compGroupName,da.ar_fullname , sc.comp_phone                                           ")
		.append("  FROM bid_project bp, bid_projcompany bpc, std_compinfo sc                                 ")
		.append("  left join  dic_area da on sc.area_id = da.area_id                                         ")
		.append("  left join  std_compgroup scg on sc.comp_group_id = scg.comp_group_id                      ")
		.append("  where bp.proj_id = bpc.proj_id                                                            ")
		.append("  AND bpc.comp_id = sc.comp_id                                                              ")
		.append("  AND bpc.status = 1                                                                        ")
		.append("  and bp.status =  1 and bp.starttime <= SYSDATE() and bp.endtime >= SYSDATE()              ")
		.append("  and sc.status = 1                                                                         ");
		
//		
//		if(StringUtils.isNotBlank(hospId)){//根据用户id查
//			hql.append(" AND bph.hosp_id = :hospId ");
//		}
//		if(StringUtils.isNotBlank(projId)){//根据项目id查
//			hql.append(" AND bpc.proj_id = :projId ");
//		}
		if(StringUtils.isNotBlank(compName)){//根据企业名查
			hql.append("and (sc.comp_name like :compName or sc.comp_spel_code like :compName )");
		}
		
		if(StringUtils.isNotBlank(compCode)){//根据企业名查
			hql.append("and sc.comp_code = :compCode ");
		}
		Query query = sessionFactory.getCurrentSession().createSQLQuery(hql.toString());
		Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(" SELECT found_rows() AS rowcount");
//		if(StringUtils.isNotBlank(hospId)){
//			//根据项目id查
//			query.setParameter("hospId",hospId);
//		}
//		if(StringUtils.isNotBlank(projId)){
//			//根据用户id查
//			query.setParameter("projId", projId);
//		}
		if(StringUtils.isNotBlank(compName)){
			//根据企业名查
			query.setParameter("compName", "%"+compName+"%");
		}
		if(StringUtils.isNotBlank(compCode)){
			//根据企业名查
			query.setParameter("compCode", compCode);
		}
		
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		//将查出来的数据与字段对应
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List list = query.list();
		int records = ((Number)countQuery.uniqueResult()).intValue();
		page.setRecords(records);
		return list;
		 
	}

	/**
	 * @category 获取所有配送企业数据
	 * @author 应晓川
	 * @date   2015年7月7日
	 * @param   page
	 * @param  List<Object>
	 */
	@Override
	public List<Object> getAllBidProjCompanyList(Pagination page) {
		
		String projId =  page.getConditions().get("projId");
		String compName =  page.getConditions().get("compName");
		String compCode = page.getConditions().get("compCode");
		StringBuffer sql = new StringBuffer();
		sql.append("  select SQL_CALC_FOUND_ROWS DISTINCT sci.comp_id AS compId,                                   ")
		.append("  sci.comp_code AS compCode,                                                                      ")
		.append("  sci.comp_name AS compName,                                                                      ")
		.append("  sci.org_code AS orgCode,                                                                        ")
		.append("  (case when scg.comp_group_name is null then '--' else scg.comp_group_name end) as compGroupName,")
		.append("  da.ar_fullname AS arFullname,                                                                   ")
		.append("  sci.comp_phone  AS compPhone                                                                    ")
		.append(" FROM bid_project bp, bid_projcompany bpc,                                                        ")
		.append(" std_compinfo sci                                                                                 ")
		.append(" left join  dic_area da on sci.area_id = da.area_id                                               ")
		.append(" left join  std_compgroup scg on sci.comp_group_id = scg.comp_group_id                            ")
		.append(" where bp.proj_id = bpc.proj_id                                                                   ")
		.append(" AND bpc.comp_id = sci.comp_id                                                                    ")
		.append(" AND bpc.status = 1                                                                               ")
		.append(" and bp.status =  1 and bp.starttime <= SYSDATE() and bp.endtime >= SYSDATE()                     ")
		.append(" and sci.status = 1                                                                               ");
		
		if(StringUtils.isNotBlank(projId)){//根据项目id查
			sql.append(" and bpc.proj_id = :projId ");
		}
		if(StringUtils.isNotBlank(compName)){//根据企业名查
			sql.append("and (sci.comp_name like :compName or sci.comp_spel_code like :compName )");
		}
		if(StringUtils.isNotBlank(compCode)){//根据企业编码
			sql.append("and sci.comp_code = :compCode ");
		}
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());
		Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(" SELECT found_rows() AS rowcount");
		if(StringUtils.isNotBlank(projId)){
			//根据用户id查
			query.setParameter("projId", projId);
		}
		if(StringUtils.isNotBlank(compName)){
			//根据企业名查
			query.setParameter("compName", "%"+compName+"%");
		}
		
		if(StringUtils.isNotBlank(compCode)){
			//根据企业名查
			query.setParameter("compCode", compCode);
		}
		query.setFirstResult((page.getPage()-1)*page.getMaxResults());
		query.setMaxResults(page.getMaxResults());
		//将查出来的数据与字段对应
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List list = query.list();
		//返回值对象转换成int类型
		int records = ((Number)countQuery.uniqueResult()).intValue();
		page.setRecords(records);
		return list;
		
	}
	
	/**
	 * @category 获取项目中有效的配送企业
	 * @date 2015年11月19日
	 * @param page
	 * @return
	 */
	public Pagination getProjCompList(Pagination page) {
		StringBuilder sb = new StringBuilder();
		sb.append(" SELECT a.comp_code compCode, a.comp_name compName, ");
		sb.append(" 	a.org_code orgCode, c.ar_fullname areaName,  ");
		sb.append(" 	a.comp_phone compPhone, d.comp_group_name groupName ");
		sb.append(" FROM std_compinfo a JOIN ( ");
		sb.append(" 	SELECT DISTINCT b.comp_code FROM bid_projcompany b  ");
		sb.append(" 	WHERE b.proj_status = 1 AND b.`status` = 1  ");
		sb.append(" 	AND b.proj_starttime <= SYSDATE() AND b.proj_endtime >= SYSDATE() ");
		sb.append(" ) b ON a.comp_code = b.comp_code  ");
		sb.append(" LEFT JOIN dic_area c ON a.area_id = c.area_id ");
		sb.append(" LEFT JOIN std_compgroup d ON a.comp_group_id = d.comp_group_id ");
		sb.append(" WHERE a.`status` = 1 ");
		
		StdCompinfo compInfo = (StdCompinfo) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		
		if (compInfo != null) {
			if (StringUtils.isNotBlank(compInfo.getCompCode())) {
				sb.append(" AND a.comp_code = :compCode ");
				params.put("compCode", compInfo.getCompCode());
			}
			if (StringUtils.isNotBlank(compInfo.getCompName())) {
				sb.append(" AND (a.comp_name LIKE :compName OR a.comp_spel_code LIKE :compName)");
				params.put("compName", "%" + compInfo.getCompName() + "%");
			}
		}
		
		dao.getPageMapList(sb.toString(), params, page);
		
		return page;
	}
	
}




